package dao;

import bean.student;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDAO {

    // SQL 语句常量
    private static final String INSERT_STUDENT_SQL = "INSERT INTO student (id,name, age, sex, className) VALUES (?,?, ?, ?, ?)";
    private static final String DELETE_STUDENT_SQL = "DELETE FROM student WHERE id = ?";
    private static final String UPDATE_STUDENT_SQL = "UPDATE student SET name = ?, age = ?, sex = ?, className = ? WHERE id = ?";
    private static final String SELECT_STUDENT_BY_ID_SQL = "SELECT * FROM student WHERE id = ?";
    private static final String SELECT_ALL_STUDENTS_SQL = "SELECT * FROM student";

    /**
     * 向 student 表插入一条记录
     * @param student 学生对象
     * @return 插入成功返回 true，否则返回 false
     */
    public static boolean insertStudent(student student) {
        boolean rowInserted = false;
        try (Connection connection = DatabaseConnection.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(INSERT_STUDENT_SQL)) {

            preparedStatement.setInt(1, student.getId());
            preparedStatement.setString(2, student.getName());
            preparedStatement.setInt(3, student.getAge());
            preparedStatement.setString(4, student.getSex());
            preparedStatement.setString(5, student.getClassName());


            rowInserted = preparedStatement.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rowInserted;
    }

    /**
     * 根据 ID 删除 student 表中的一条记录
     * @param id 学生ID
     * @return 删除成功返回 true，否则返回 false
     */
    public static void deleteStudent(int id) {

        try (Connection connection = DatabaseConnection.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(DELETE_STUDENT_SQL)) {
            preparedStatement.setInt(1, id);
            int rowsDeleted = preparedStatement.executeUpdate();
            if (rowsDeleted > 0) {
                System.out.println("操作成功");
            } else {
                System.out.println("未找到指定的学生记录");
            }
        } catch (SQLException e) {

            e.printStackTrace();
        }

    }

    /**
     * 更新 student 表中的一条记录
     * @param student 学生对象
     * @return 更新成功返回 true，否则返回 false
     */
    public boolean updateStudent(student student) {
        boolean rowUpdated = false;
        try (Connection connection = DatabaseConnection.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_STUDENT_SQL)) {
            preparedStatement.setString(1, student.getName());
            preparedStatement.setInt(2, student.getAge());
            preparedStatement.setString(3, student.getSex());
            preparedStatement.setString(4, student.getClassName());
            preparedStatement.setInt(5, student.getId());

            rowUpdated = preparedStatement.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rowUpdated;
    }

    public static void printAllStudent()
    {
        List<student> students = selectAllStudents();
        for (student student : students) {
            System.out.println(student.toString());
        }
    }
    public static void searchId(int id)
    {
        student student = selectStudent(id);
        if(student!=null)
        {
            System.out.println(student.toString());
        }
        else
        {
            System.out.println("未找到该学生");
            //执行插入方法，插入一个id为1的学生
            student s=new student(1,"小明",18,"男","1班");
             insertStudent(s);
             //这里输入一个id为1的学生，如果数据库中不存在，则插入，同时student s，输入s即可
            System.out.println("已插入该1学生");
        }
    }
    /**
     * 根据 ID 查询 student 表中的一条记录
     * @param id 学生ID
     * @return 学生对象，如果没有找到则返回 null
     */
    public static student selectStudent(int id) {
        student student = null;
        try (Connection connection = DatabaseConnection.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(SELECT_STUDENT_BY_ID_SQL)) {
            preparedStatement.setInt(1, id);
            ResultSet resultSet = preparedStatement.executeQuery();

            if (resultSet.next()) {
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                String sex = resultSet.getString("sex");
                String className = resultSet.getString("className");

                student = new student(id, name, age, sex, className);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return student;
    }

    /**
     * 查询 student 表中的所有记录
     * @return 学生对象列表
     */
    public static List<student> selectAllStudents() {
        List<student> students = new ArrayList<>();
        try (Connection connection = DatabaseConnection.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(SELECT_ALL_STUDENTS_SQL)) {
            ResultSet resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                String sex = resultSet.getString("sex");
                String className = resultSet.getString("className");

                students.add(new student(id, name, age, sex, className));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return students;
    }
}
